
DECLARE @TableName varchar(50)
Declare @ColumnName varchar(50)
Declare @IsIdentity bit
Declare @DataType as varchar(50)
Declare @MaxLength as int
Declare @IsNullable as bit
Declare @NumbericPrecision as int

Declare curTableList Cursor For
SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_NAME

Open curTableList

PRINT '<html><body>'

FETCH NEXT FROM curTableList INTO @TableName

WHILE @@FETCH_STATUS <> -1
BEGIN

	PRINT '<h3>' + @TableName + '</h3>'
	PRINT '<table><th>COLUMN NAME</th><th>DATA TYPE</th><th>IS_IDENTITY</th><th>IS NULLABLE</th><th>LENGHT</th><th>NUMBERIC PRECISION</th>'
	Declare curTableInfo Cursor FOR
	--SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH, IS_NULLABLE
	--FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = @TableName
	--ORDER BY TABLE_NAME, COLUMN_NAME  
	SELECT
	CLMNS.NAME AS [NAME],
	USRT.NAME AS [DATATYPE],
	CLMNS.IS_IDENTITY AS [IS_IDENTITY],
	--ISNULL(BASET.NAME, N'') AS [SYSTEMTYPE],
	CLMNS.Is_Nullable,
	CAST(CASE WHEN BASET.NAME IN (N'NCHAR', N'NVARCHAR') AND CLMNS.MAX_LENGTH <> -1 THEN
	CLMNS.MAX_LENGTH/2 ELSE CLMNS.MAX_LENGTH END AS INT) AS [LENGTH],
	CAST(CLMNS.PRECISION AS INT) AS [NUMERICPRECISION]
	FROM
	SYS.TABLES AS TBL
	INNER JOIN SYS.ALL_COLUMNS AS CLMNS ON CLMNS.OBJECT_ID=TBL.OBJECT_ID
	LEFT OUTER JOIN SYS.TYPES AS USRT ON USRT.USER_TYPE_ID = CLMNS.USER_TYPE_ID
	LEFT OUTER JOIN SYS.TYPES AS BASET ON BASET.USER_TYPE_ID = CLMNS.SYSTEM_TYPE_ID AND 
	BASET.USER_TYPE_ID = BASET.SYSTEM_TYPE_ID
	WHERE
	(TBL.NAME=@TABLENAME AND SCHEMA_NAME(TBL.SCHEMA_ID)=N'DBO')
	ORDER BY
	CLMNS.COLUMN_ID ASC

	Open curTableInfo
	
	FETCH NEXT FROM curTableInfo INTO @ColumnName, @DataType, @IsIdentity,  @IsNullable, @MaxLength, @NumbericPrecision
	
	WHILE @@FETCH_STATUS <> -1
	BEGIN
		PRINT '<tr><td>' + @ColumnName + '</td><td>' + @DataType + '</td><td>' + CASE @IsIdentity WHEN 1 THEN 'YES' ELSE 'NO' END + '</td><td>' + CASE @IsNullable WHEN 1 THEN 'Yes' ELSE 'No' END + '</td><td>' + Cast(@MaxLength as varchar) + '</td><td>' + Cast(@NumbericPrecision as varchar) + '</td></tr>'
		FETCH NEXT FROM curTableInfo INTO @ColumnName, @DataType, @IsIdentity,  @IsNullable, @MaxLength, @NumbericPrecision
	END
	PRINT '</table><br />'
	
	Close curTableInfo
	Deallocate curTableInfo
	FETCH NEXT FROM curTableList INTO @TableName
END
PRINT '</body></html>'
Close curTableList
Deallocate curTableList


--SELECT name, object_id, principal_id, schema_id, parent_object_id, type, type_desc, create_date, modify_date, is_ms_shipped, is_published,
--is_schema_published
--FROM sys.all_objects
--WHERE (is_ms_shipped = 0) AND (type_desc = N'SQL_STORED_PROCEDURE')
--Order By modify_date desc

--select top 10 * from INFORMATION_SCHEMA.PARAMETERS Where SPECIFIC_NAME = 'sprocProviderCarOccupationSelectItemList'

--EXEC sp_helptext 'sprocProviderCarOccupationSelectItemList'

--Exec Sp_help 'sprocProviderCarOccupationSelectItemList'